﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DataCombine
{
    public enum DataCombineType
    {
        SameString,
        DateWeek,
        DateMonth,
        DateYear
    }

    public partial class DataCombineMainForm : Form
    {
        DataTable date_number_datatable;
        DataTable dtWeekCombined;
        DataTable dtMonthCombined;
        DataTable dtYearCombined;

        public string filePath { set; get; }
        FileStream stream;
        public IWorkbook workbook;
        public bool isCompatible;
        public List<string> activateSheetNames = new List<string>();
        DataSet dataOfAllSheets = new DataSet();

        public DataCombineMainForm()
        {
            InitializeComponent();
            this.Icon = Properties.Resources.MainLogo;
        }

        #region util and test 
        private string selectExcelFile()
        {
            OpenFileDialog file = new OpenFileDialog();
            file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            if (string.IsNullOrEmpty(Properties.Settings.Default.LastTimeFolder))
            {
                file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            }
            else
            {
                file.InitialDirectory = Properties.Settings.Default.LastTimeFolder;
            }

            file.Multiselect = false;

            if (file.ShowDialog() == DialogResult.Cancel)
                return "";

            return file.FileName; ;
        }

        public static DataTable getTestDataTable()
        {
            DataTable tdt = new DataTable();

            DataColumn dateDataColumn = tdt.Columns.Add("Date");
            DataColumn countDataColumn = tdt.Columns.Add("Count");
            Random rd = new Random();

            for (int i = 1; i <= 31; i++)
            {
                DataRow dr = tdt.NewRow();
                dr[0] = string.Format("2021-05-{0:D2}", i);
                dr[1] = rd.Next(1000, 3000);
                tdt.Rows.Add(dr);
            }

            for (int i = 1; i <= 30; i++)
            {
                DataRow dr = tdt.NewRow();
                dr[0] = string.Format("2021/06/{0:D2}", i);
                dr[1] = rd.Next(1000, 3000);
                tdt.Rows.Add(dr);
            }

            return tdt;
        }

        public static DataTable datatableDateFormat(DataTable dt)
        {
            DataTable dtf = dt.Copy();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string dateString = dt.Rows[i][0].ToString();
                Console.WriteLine(dateString);
                DateTime date;
                
                #region Method 0

                bool pr = DateTime.TryParse(dateString, out date);
                if(pr)
                {
                    dtf.Rows[i][0] = date;
                } 
                else
                {
                    MessageBox.Show("日期格式解析失败\nTODO: 需要手动修正错误\n" + dt.Rows[i][0].ToString());
                    return null;
                }

                #endregion

                #region Method 1
                /*
                try
                {
                    date = DateTime.Parse(dateString);
                    dtf.Rows[i][0] = date;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString() + "\nTODO: 需要手动修正错误\n" + dt.Rows[i][0].ToString());
                    return null;
                }
                */
                #endregion

                #region Method 2

                /*string[] formats = { "yyyy-MM-dd", "dd/MM/yyyy", "yyyy年MM月dd日", "yyyy年M月d日", "dd-MMM-yyyy" };
                //CultureInfo culture = CultureInfo.CreateSpecificCulture("en-US");
                CultureInfo culture = CultureInfo.CreateSpecificCulture("zh_CN");
                string dateString = dt.Rows[i][0].ToString();
                Console.WriteLine(dateString);
                
                DateTime date;
                bool r = false;
                
                int eceptionCount = 0;
                for(int j = 0; j < formats.Length; j++ )
                {
                    r = DateTime.TryParseExact(dateString, formats[i], culture, DateTimeStyles.None, out date);
                    if(r == true)
                    {
                        dtf.Rows[i][0] = date;
                        break;
                    } else
                    {
                        eceptionCount++;
                        Console.WriteLine("try fail: "+ eceptionCount.ToString());
                    }
                }

                if (eceptionCount >= formats.Length)
                {
                    MessageBox.Show("Find String cannot convert");
                }*/

                #endregion


            }

            return dtf;
        }

        public static List<int> getSelectedRowIndexs(DataGridView dgv)
        {
            List<int> list = new List<int>();
            foreach (DataGridViewRow dgvr in dgv.SelectedRows)
            {
                list.Add(dgvr.Index);
            }
            return list;
        }

        private static DataTable getSelectSortTable(DataTable dti, DataGridView dgv)
        {
            if (dti == null || dgv == null)
            {
                MessageBox.Show("无数据");
                return null;
            }
            DataTable dto = new DataTable();

            string c0n = dti.Columns[0].ColumnName;
            dto.Columns.Add(c0n);
            string c1n = dti.Columns[1].ColumnName;
            dto.Columns.Add(c1n);
            foreach (DataGridViewRow dgvr in dgv.SelectedRows)
            {
                if(dgvr.IsNewRow == true)
                {
                    continue;
                }
                int indexInView = dgvr.Index;
                dto.ImportRow(dti.Rows[indexInView]);
            }

            DataView dv = dto.DefaultView;
            dv.Sort = string.Format("{0} Asc", c0n);
            DataTable dts = dv.ToTable();

            return dts;
        }

        bool importFile(string filePath)
        {
            bool rval = true;

            #region make workbook from file
            try
            {
                stream = System.IO.File.OpenRead(filePath);
                isCompatible = filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);

                if (isCompatible)
                {
                    workbook = new HSSFWorkbook(stream);
                }
                else
                {
                    workbook = new XSSFWorkbook(stream);
                }

                stream.Close();
            }
            catch
            {
                MessageBox.Show("请关闭文件,再导入！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }
            finally
            {
                if (stream != null)
                {
                    stream.Close();
                }

            }

            string wbVer = workbook.SpreadsheetVersion.ToString();
            string wbSheetsCount = workbook.NumberOfSheets.ToString();
            string wbInfo = string.Format("Workbook version={0} SheetCount={1}", wbVer, wbSheetsCount);
            Console.WriteLine(wbInfo);
            #endregion

            #region get all sheet names
            activateSheetNames = ExcelHelper.GetSheetNames(workbook);
            Console.WriteLine("find sheets: ");
            foreach (string s in activateSheetNames)
            {
                Console.WriteLine(s);
            }
            #endregion

            #region select sheet(name)
            Form_SheetSelect fss = new Form_SheetSelect(activateSheetNames);
            fss.Owner = this;

            DialogResult dr = fss.ShowDialog();

            if (dr == DialogResult.OK)
            {
                Console.WriteLine("sheet select ok");

                if (activateSheetNames.Count == 0)
                {
                    Console.WriteLine("no sheet is selected");
                    return false;
                }

                activateSheetNames = fss.selectedSheetNames;
                Console.WriteLine("selected sheets: ");
                foreach (string s in activateSheetNames)
                {
                    Console.WriteLine(s);
                }
            }
            else
            {
                Console.WriteLine("sheet select cancel");
                return false;
            }

            #endregion

            #region Make DataTable from activate sheet names

            dataOfAllSheets = new DataSet();
            foreach (string sn in activateSheetNames)
            {
                ISheet sheet = workbook.GetSheet(sn);
                DataTable dt = ExcelHelper.GetDataTableFromSheet(sheet, 2);
                dataOfAllSheets.Tables.Add(dt);
            }
            Console.WriteLine("Datatable count:" + dataOfAllSheets.Tables.Count.ToString());
            #endregion

            #region load to datagridview
            if (dataOfAllSheets.Tables.Count > 0)
            {
                date_number_datatable = dataOfAllSheets.Tables[0];
                //date_number_datatable = datatableDateFormat(dataOfAllSheets.Tables[0]);

                dataGridView1.DataSource = null;
                dataGridView1.Columns.Clear();
                dataGridView1.DataSource = date_number_datatable;

                foreach (DataGridViewColumn dgvc in dataGridView1.Columns)
                {
                    dgvc.SortMode = DataGridViewColumnSortMode.NotSortable;
                }
            }
            else
            {
                MessageBox.Show("没有DataTable");
                return false;
            }
            Console.WriteLine("数据载入结束");
            #endregion


            toolStripStatusLabel1.Text = "载入的文档: " + filePath;

            return rval;
        }

        public static void DelectDir(string srcPath)
        {
            try
            {
                DirectoryInfo dir = new DirectoryInfo(srcPath);
                FileSystemInfo[] fileinfo = dir.GetFileSystemInfos();  //返回目录中所有文件和子目录
                foreach (FileSystemInfo i in fileinfo)
                {
                    if (i is DirectoryInfo)            //判断是否文件夹
                    {
                        DirectoryInfo subdir = new DirectoryInfo(i.FullName);
                        subdir.Delete(true);          //删除子目录和文件
                    }
                    else
                    {
                        File.Delete(i.FullName);      //删除指定文件
                    }
                }
            }
            catch (Exception e)
            {
                throw;
            }
        }
        #endregion

        #region Buttons
        private void button_Week_Combine_Click(object sender, EventArgs e)
        {
            if(date_number_datatable == null)
            {
                MessageBox.Show("请使用 文件>打开 导入数据");
                return;
            }
            DataTable dtf = date_number_datatable.Copy();

            var keys = new DataColumn[1];
            dtWeekCombined = new DataTable();
            DataColumn column;

            column = new DataColumn();
            column.DataType = Type.GetType("System.String");
            column.ColumnName = "年-周";
            dtWeekCombined.Columns.Add(column);
            keys[0] = column;

            column = new DataColumn();
            column.DataType = Type.GetType("System.Int32");
            column.ColumnName = "数量";
            dtWeekCombined.Columns.Add(column);
            //keys[1] = column;

            dtWeekCombined.PrimaryKey = keys;

            for (int i = 0; i < dtf.Rows.Count; i++)
            {
                int value = 0;
                try
                {
                    value = int.Parse(dtf.Rows[i][1].ToString());
                }
                catch
                {
                    MessageBox.Show("表格数据有错误! 行号:" + i.ToString());
                    continue;
                }
                
                DateTime dateOfRow = DateTime.Parse(dtf.Rows[i][0].ToString());

                int year = dateOfRow.Year;
                GregorianCalendar gc = new GregorianCalendar();
                int weekOfYear = gc.GetWeekOfYear(dateOfRow, CalendarWeekRule.FirstDay, DayOfWeek.Monday);

                string key = string.Format("{0}-{1:D2}", year, weekOfYear);
                DataRow dr = dtWeekCombined.Rows.Find(key);
                if (dr != null)
                {
                    //已经有这个年-周的key了.  插入
                    dr[1] = (int)dr[1] + value;
                }
                else
                {
                    //新的一周, 新建一个row
                    DataRow newRow = dtWeekCombined.NewRow();
                    newRow[0] = key;
                    newRow[1] = value;
                    dtWeekCombined.Rows.Add(newRow);
                }
            }

            dataGridView2.Columns.Clear();
            dataGridView2.DataSource = dtWeekCombined;
        }

        private void button_Month_Combine_Click(object sender, EventArgs e)
        {
            if (date_number_datatable == null)
            {
                MessageBox.Show("请使用 文件>打开 导入数据");
                return;
            }
            DataTable dtf = date_number_datatable.Copy();

            var keys = new DataColumn[1];
            
            dtMonthCombined = new DataTable();
            DataColumn column;

            column = new DataColumn();
            column.DataType = Type.GetType("System.String");
            column.ColumnName = "年-月";
            dtMonthCombined.Columns.Add(column);
            keys[0] = column;

            column = new DataColumn();
            column.DataType = Type.GetType("System.Int32");
            column.ColumnName = "数量";
            dtMonthCombined.Columns.Add(column);
            //keys[1] = column;

            dtMonthCombined.PrimaryKey = keys;

            for (int i = 0; i < dtf.Rows.Count; i++)
            {
                int value = 0;
                try
                {
                    value = int.Parse(dtf.Rows[i][1].ToString());
                }
                catch
                {
                    MessageBox.Show("表格数据有错误! 行号:" + i.ToString());
                    continue;
                }
                DateTime dateOfRow = DateTime.Parse(dtf.Rows[i][0].ToString());

                int year = dateOfRow.Year;
                int month = dateOfRow.Month;
                //GregorianCalendar gc = new GregorianCalendar();
                //int weekOfYear = gc.GetWeekOfYear(dateOfRow, CalendarWeekRule.FirstDay, DayOfWeek.Monday);

                string key = string.Format("{0}-{1:D2}", year, month);
                DataRow dr = dtMonthCombined.Rows.Find(key);
                if (dr != null)
                {
                    //已经有这个年-周的key了.  插入
                    dr[1] = (int)dr[1] + value;
                }
                else
                {
                    //新的一周, 新建一个row
                    DataRow newRow = dtMonthCombined.NewRow();
                    newRow[0] = key;
                    newRow[1] = value;
                    dtMonthCombined.Rows.Add(newRow);
                }
            }

            dataGridView3.Columns.Clear();
            dataGridView3.DataSource = dtMonthCombined;
        }

        private void button_Year_Combine_Click(object sender, EventArgs e)
        {
            if (date_number_datatable == null)
            {
                MessageBox.Show("请使用 文件>打开 导入数据");
                return;
            }
            DataTable dtf = date_number_datatable.Copy();

            var keys = new DataColumn[1];
             dtYearCombined = new DataTable();
            DataColumn column;

            column = new DataColumn();
            column.DataType = Type.GetType("System.String");
            column.ColumnName = "年度";
            dtYearCombined.Columns.Add(column);
            keys[0] = column;

            column = new DataColumn();
            column.DataType = Type.GetType("System.Int32");
            column.ColumnName = "数量";
            dtYearCombined.Columns.Add(column);
            //keys[1] = column;

            dtYearCombined.PrimaryKey = keys;

            for (int i = 0; i < dtf.Rows.Count; i++)
            {
                int value = 0;
                try
                {
                    value = int.Parse(dtf.Rows[i][1].ToString());
                }
                catch
                {
                    MessageBox.Show("表格数据有错误! 行号:" + i.ToString());
                    continue;
                }
                DateTime dateOfRow = DateTime.Parse(dtf.Rows[i][0].ToString());

                int year = dateOfRow.Year;
                //int month = dateOfRow.Month;

                string key = string.Format("{0}", year);
                DataRow dr = dtYearCombined.Rows.Find(key);
                if (dr != null)
                {
                    //已经有这个年-周的key了.  插入
                    dr[1] = (int)dr[1] + value;
                }
                else
                {
                    //新的一周, 新建一个row
                    DataRow newRow = dtYearCombined.NewRow();
                    newRow[0] = key;
                    newRow[1] = value;
                    dtYearCombined.Rows.Add(newRow);
                }
            }

            dataGridView4.Columns.Clear();
            dataGridView4.DataSource = dtYearCombined;
        }
        
        private void button_Chart_Week_Click(object sender, EventArgs e)
        {            
             Form_Chart fc = new Form_Chart(getSelectSortTable(dtWeekCombined, dataGridView2)); 
             fc.Show();
        }

        private void button_Chart_Month_Click(object sender, EventArgs e)
        {
            Form_Chart fc = new Form_Chart(getSelectSortTable(dtMonthCombined, dataGridView3));
            fc.Show();
        }

        private void button_Chart_Year_Click(object sender, EventArgs e)
        {
            Form_Chart fc = new Form_Chart(getSelectSortTable(dtYearCombined, dataGridView4));
            fc.Show();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Coming Soooooon!");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Coming Soooooon!");
        }

        private void button4_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Coming Soooooon!");
        }

        #endregion

        #region menu items

        /// <summary>
        /// 打开要载入的文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ToolStripMenuItem_Click_File_Open(object sender, EventArgs e)
        {
            #region 文件打开
            filePath = selectExcelFile();
            
            if (!File.Exists(filePath))
            {
                MessageBox.Show("文件不存在,请重新选择 " + filePath);
                return;
            }

            string extension = Path.GetExtension(filePath);
            if (!filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase) && !filePath.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                MessageBox.Show("文件扩展名错误, 请重新选择 " + extension);
                return;
            }

            string folder = Path.GetDirectoryName(filePath);
            Properties.Settings.Default.LastTimeFolder = folder;
            Properties.Settings.Default.Save();
            #endregion
            /*
            #region make workbook from file
            try
            {
                stream = System.IO.File.OpenRead(filePath);
                isCompatible = filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);

                if (isCompatible)
                {
                    workbook = new HSSFWorkbook(stream);
                }
                else
                {
                    workbook = new XSSFWorkbook(stream);
                }

                stream.Close();
            }
            catch
            {
                MessageBox.Show("请关闭文件,再导入！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                if (stream != null)
                {
                    stream.Close();
                }

            }

            string wbVer = workbook.SpreadsheetVersion.ToString();
            string wbSheetsCount = workbook.NumberOfSheets.ToString();
            string wbInfo = string.Format("Workbook version={0} SheetCount={1}", wbVer, wbSheetsCount);
            Console.WriteLine(wbInfo);
            #endregion

            #region get all sheet names
            activateSheetNames = ExcelHelper.GetSheetNames(workbook);
            Console.WriteLine("find sheets: ");
            foreach(string s in activateSheetNames)
            {
                Console.WriteLine(s);
            }
            #endregion

            #region select sheet(name)
            Form_SheetSelect fss = new Form_SheetSelect(activateSheetNames);
            fss.Owner = this;

            DialogResult dr = fss.ShowDialog();

            if (dr == DialogResult.OK)
            {
                Console.WriteLine("sheet select ok");

                if (activateSheetNames.Count == 0)
                {
                    Console.WriteLine("no sheet is selected");
                    return;
                }

                activateSheetNames = fss.selectedSheetNames;
                Console.WriteLine("selected sheets: ");
                foreach (string s in activateSheetNames)
                {
                    Console.WriteLine(s);
                }
            }
            else
            {
                Console.WriteLine("sheet select cancel");
                return;
            }

            #endregion

            #region Make DataTable from activate sheet names

            dataOfAllSheets = new DataSet();
            foreach (string sn in activateSheetNames)
            {
                ISheet sheet = workbook.GetSheet(sn);
                DataTable dt = ExcelHelper.GetDataTableFromSheet(sheet, 2);
                dataOfAllSheets.Tables.Add(dt);
            }
            Console.WriteLine("Datatable count:" + dataOfAllSheets.Tables.Count.ToString());
            #endregion

            #region load to datagridview
            if (dataOfAllSheets.Tables.Count > 0)
            {
                date_number_datatable = dataOfAllSheets.Tables[0];
                //date_number_datatable = datatableDateFormat(dataOfAllSheets.Tables[0]);

                dataGridView1.DataSource = null;
                dataGridView1.Columns.Clear();
                dataGridView1.DataSource = date_number_datatable;

                foreach (DataGridViewColumn dgvc in dataGridView1.Columns)
                {
                    dgvc.SortMode = DataGridViewColumnSortMode.NotSortable;
                }
            } else
            {
                MessageBox.Show("没有DataTable");
                return;
            }
            Console.WriteLine("数据载入结束");
            #endregion
            */

            bool result = importFile(filePath);

            if(result)
            {
                Properties.Settings.Default.LastFilePath = filePath;
                Properties.Settings.Default.Save();
            }
        }

        /// <summary>
        /// 载入上一次的文档
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ToolStripMenuItem_LoadLastFile_Click(object sender, EventArgs e)
        {
            importFile(Properties.Settings.Default.LastFilePath);
        }

        private void ToolStripMenuItem_Click_File_Exit(object sender, EventArgs e)
        {
            this.Close();
        }
        private void ToolStripMenuItem_Option_LoadTestDate(object sender, EventArgs e)
        {
            // 拿到原始数据, 日期为字符串
            date_number_datatable = getTestDataTable();

            foreach (DataRow dr in date_number_datatable.Rows)
            {
                Console.WriteLine(dr.ToString());
            }

            dataGridView1.Columns.Clear();
            dataGridView1.DataSource = date_number_datatable;
        }
        private void ToolStripMenuItem_PrintInternalData_Click(object sender, EventArgs e)
        {
            Console.WriteLine("ToolStripMenuItem_PrintInternalData_Click " + e.ToString());

            if (date_number_datatable != null)
            {
                int rowCount = date_number_datatable.Rows.Count;
                Console.WriteLine("内部数据共 " + rowCount.ToString() + "行");

                if (rowCount > 0)
                {
                    for (int rn = 0; rn < rowCount; rn++)
                    {
                        DataRow dr = date_number_datatable.Rows[rn];
                        string s0 = dr[0].ToString();
                        string s1 = dr[1].ToString();
                        string s2 = dr[0].GetType().ToString();
                        string sa = string.Format("R:<{0}> D0:<{1}> D1:<{2}> D0Type:<{3}>", rn, s0, s1, s2);
                        Console.WriteLine(sa);
                    }
                }
            }
            else
            {
                Console.WriteLine("还没有载入数据");
            }
        }

        private void ToolStripMenuItem_About_Click(object sender, EventArgs e)
        {
            AboutBox1 about = new AboutBox1();
            about.Show();
        }

        private void ToolStripMenuItem_Help_Click(object sender, EventArgs e)
        {
            AboutBox1.goToSite();
        }




        #endregion

        private void ToolStripMenuItem_ClearClipboardTempFolder_Click(object sender, EventArgs e)
        {
            string folderName = Properties.Settings.Default.ClipboardTempFolderPath;

            if (Directory.Exists(folderName))
            {
                DelectDir(folderName);
            }
        }
    }
}
